﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;


namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class ServiceCheckTempDBdatafilesLessProcessorsCount:IssueTypeCheckBase
    {

        public ServiceCheckTempDBdatafilesLessProcessorsCount()
        {
            IssueTypeId = 2004;
        }

        public override void Check()
        {
            // Service check tempdb should have the number of datafiles  equal to the number of physical processor
            HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);


            /*
             * This rule checks that the amount of tempdb data files are equal or more than the number of physical processors
             */

            var Qry = (from s in dc.Servers
                       from sv in s.Services
                      
                       from d in sv.Databases
                       from df in d.DatabaseFiles



                       where d.DatabaseName=="tempdb"  && !df.File.EndsWith("ldf")
                       && sv.ServiceTypeID == 1

                       group s by new { s.ServerID,s.Name,s.ProcessorCount }
                           into groupServerIDs
                           select new
                           {

                               serviceID = groupServerIDs.Key.ServerID,
                               serviceName = groupServerIDs.Key.Name,
                               Tempdbcount = groupServerIDs.Count(),
                               processerCount = groupServerIDs.Key.ProcessorCount,

                           });
            
            

            foreach (var item in Qry)
            {
                if (item.processerCount != item.Tempdbcount)
                {
                    IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);

                    //Create new Issue

                    Issue issue = new Issue();
                    issue.IssueType = issueType;
                    issue.TargetId = item.serviceID;
                    
                    issue.Description = issueType.Description.Replace("{InstanceName}",item.serviceName).Replace("{counttempdb}", item.Tempdbcount.ToString()).Replace("{procCount}", item.processerCount.ToString());

                    dc.Issues.InsertOnSubmit(issue);
                }
            }

            dc.SubmitChanges();
        
        }
    
    }
}
